using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VICC_VKCOST
    {
        public DataRow Convert2ICC_VKCOST(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryICC_VKCOST(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "JLT":
                    #region=========
         dwReturn["ROWNID"] = " ".PadRight(0 ); 
         dwReturn["MANDANT"] = " ".PadRight(2 ); 
         dwReturn["INVDAT"] = " ".PadRight(10 ); 
         dwReturn["DELDAT"] = " ".PadRight(10 ); 
         dwReturn["INVOICE"] = " ".PadRight(15 ); 
         dwReturn["VKPAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["VKPAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["VKPKOLL"] = " ".PadRight(5 ); 
         dwReturn["VKPART"] = " ".PadRight(24 ); 
         dwReturn["VAABEZ"] = " ".PadRight(30 ); 
         dwReturn["CONSTRUCTION"] = " ".PadRight(200 ); 
         dwReturn["COLOR"] = " ".PadRight(50 ); 
         dwReturn["YARN"] = " ".PadRight(50 ); 
         dwReturn["RATE"] = " ".PadRight(0 ); 
         dwReturn["FACTORY"] = " ".PadRight(10 ); 
         dwReturn["WARPDEN"] = " ".PadRight(0 ); 
         dwReturn["WEFTDEN"] = " ".PadRight(0 ); 
         dwReturn["MKUNDER"] = " ".PadRight(8 ); 
         dwReturn["KDKNAME"] = " ".PadRight(8 ); 
         dwReturn["VKPQTY"] = " ".PadRight(0 ); 
         dwReturn["TYPE"] = " ".PadRight(20 ); 
         dwReturn["CURRENCY"] = " ".PadRight(3 ); 
         dwReturn["PREH"] = " ".PadRight(3 ); 
         dwReturn["MDELQTY"] = " ".PadRight(0 ); 
         dwReturn["YDELQTY"] = " ".PadRight(0 ); 
         dwReturn["WEFTTOT"] = " ".PadRight(0 ); 
         dwReturn["WARPUSAGE"] = " ".PadRight(0 ); 
         dwReturn["WARPPRICE"] = " ".PadRight(0 ); 
         dwReturn["WEFTUSAGE"] = " ".PadRight(0 ); 
         dwReturn["WEFTPRICE"] = " ".PadRight(0 ); 
         dwReturn["YARNVALUE"] = " ".PadRight(0 ); 
         dwReturn["WARPED"] = " ".PadRight(0 ); 
         dwReturn["WEFTED"] = " ".PadRight(0 ); 
         dwReturn["DYEVALUE"] = " ".PadRight(0 ); 
         dwReturn["MCOSTFIN"] = " ".PadRight(0 ); 
         dwReturn["MCOSTPAKVV"] = " ".PadRight(0 ); 
         dwReturn["MCOSTFADD"] = " ".PadRight(0 ); 
         dwReturn["MPRICEAGENT"] = " ".PadRight(0 ); 
         dwReturn["MCOSTAGENT"] = " ".PadRight(0 ); 
         dwReturn["MPRICESHIP"] = " ".PadRight(0 ); 
         dwReturn["MCOSTSHIP"] = " ".PadRight(0 ); 
         dwReturn["MPRICEFLIGHT"] = " ".PadRight(0 ); 
         dwReturn["MCOSTFLIGHT"] = " ".PadRight(0 ); 
         dwReturn["MCOSTADD"] = " ".PadRight(0 ); 
         dwReturn["MCOSTUNIT"] = " ".PadRight(0 ); 
         dwReturn["RMBPRICE"] = " ".PadRight(0 ); 
         dwReturn["MCOSTGROSS"] = " ".PadRight(0 ); 
         dwReturn["MCOSTNET"] = " ".PadRight(0 ); 
         dwReturn["COSTTOT"] = " ".PadRight(0 ); 
         dwReturn["COSTTOTW"] = " ".PadRight(0 ); 
         dwReturn["STAFF"] = " ".PadRight(20 ); 
         dwReturn["COMMISSION"] = " ".PadRight(20 ); 
         dwReturn["NEWART"] = " ".PadRight(20 ); 
         dwReturn["PRICE"] = " ".PadRight(0 ); 
         dwReturn["INVVALUE"] = " ".PadRight(0 ); 
         dwReturn["RMBVALUE"] = " ".PadRight(0 ); 
         dwReturn["REMARK"] = " ".PadRight(200 ); 
         dwReturn["PRODVALUE"] = " ".PadRight(0 ); 
         dwReturn["OTHERSVALUE"] = " ".PadRight(0 ); 
         dwReturn["NM1"] = " ".PadRight(0 ); 
         dwReturn["NM2"] = " ".PadRight(0 ); 
         dwReturn["NM3"] = " ".PadRight(0 ); 
         dwReturn["NM4"] = " ".PadRight(0 ); 
         dwReturn["NM5"] = " ".PadRight(0 ); 
         dwReturn["CHAR1"] = " ".PadRight(20 ); 
         dwReturn["CHAR2"] = " ".PadRight(20 ); 
         dwReturn["CHAR3"] = " ".PadRight(20 ); 
         dwReturn["CHAR4"] = " ".PadRight(20 ); 
         dwReturn["CHAR5"] = " ".PadRight(20 ); 
         dwReturn["NEWUSER"] = " ".PadRight(8 ); 
         dwReturn["NEWTIME"] = " ".PadRight(20 ); 
         dwReturn["CHGUSER"] = " ".PadRight(8 ); 
         dwReturn["CHGTIME"] = " ".PadRight(20 ); 
                    #endregion
                    break;
                
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapICC_VKCOST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_VKCOST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryICC_VKCOST(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapICC_VKCOST(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertICC_VKCOST(dwLocal, ITXDBVERSION);
            }
            UpdateICC_VKCOST(dwLocal);
        }
        public void InsertICC_VKCOST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "JLT":
                    or1.CommandText = "INSERT INTO ICC_VKCOST(ROWNID,MANDANT,INVDAT,DELDAT,INVOICE,VKPAUFKRS,VKPAUFLFD,VKPKOLL,VKPART,VAABEZ,CONSTRUCTION,COLOR,"
                        + " YARN,RATE,FACTORY,WARPDEN,WEFTDEN,MKUNDER,KDKNAME,VKPQTY,TYPE,CURRENCY,PREH,MDELQTY,YDELQTY,WEFTTOT,WARPUSAGE,WARPPRICE,WEFTUSAGE,"
                        + " WEFTPRICE,YARNVALUE,WARPED,WEFTED,DYEVALUE,MCOSTFIN,MCOSTPAKVV,MCOSTFADD,MPRICEAGENT,MCOSTAGENT,MPRICESHIP,MCOSTSHIP,MPRICEFLIGHT,"
                        + " MCOSTFLIGHT,MCOSTADD,MCOSTUNIT,RMBPRICE,MCOSTGROSS,MCOSTNET,COSTTOT,COSTTOTW,STAFF,COMMISSION,NEWART,PRICE,INVVALUE,RMBVALUE,REMARK,"
                        + " PRODVALUE,OTHERSVALUE,NM1,NM2,NM3,NM4,NM5,CHAR1,CHAR2,CHAR3,CHAR4,CHAR5,NEWUSER,NEWTIME,CHGUSER,CHGTIME) "
                        + " VALUES (:ROWNID:,:MANDANT:,:INVDAT:,:DELDAT:,:INVOICE:,:VKPAUFKRS:,:VKPAUFLFD:,:VKPKOLL:,:VKPART:,:VAABEZ:,:CONSTRUCTION:,:COLOR:,"
                        + " :YARN:,:RATE:,:FACTORY:,:WARPDEN:,:WEFTDEN:,:MKUNDER:,:KDKNAME:,:VKPQTY:,:TYPE:,:CURRENCY:,:PREH:,:MDELQTY:,:YDELQTY:,:WEFTTOT:,:WARPUSAGE:,"
                        + " :WARPPRICE:,:WEFTUSAGE:,:WEFTPRICE:,:YARNVALUE:,:WARPED:,:WEFTED:,:DYEVALUE:,:MCOSTFIN:,:MCOSTPAKVV:,:MCOSTFADD:,:MPRICEAGENT:,:MCOSTAGENT:,"
                        + " :MPRICESHIP:,:MCOSTSHIP:,:MPRICEFLIGHT:,:MCOSTFLIGHT:,:MCOSTADD:,:MCOSTUNIT:,:RMBPRICE:,:MCOSTGROSS:,:MCOSTNET:,:COSTTOT:,:COSTTOTW:,:STAFF:,"
                        + " :COMMISSION:,:NEWART:,:PRICE:,:INVVALUE:,:RMBVALUE:,:REMARK:,:PRODVALUE:,:OTHERSVALUE:,:NM1:,:NM2:,:NM3:,:NM4:,:NM5:,:CHAR1:,:CHAR2:,:CHAR3:,"
                        + " :CHAR4:,:CHAR5:,:NEWUSER:,:NEWTIME:,:CHGUSER:,:CHGTIME:) ";

                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_VKCOST(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM ICC_VKCOST WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT ICC_VKCOST_SEQ.NEXTVAL AS ROWNID FROM DUAL ";
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_VKCOST WHERE VKPART = :VKPART: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM ICC_VKCOST WHERE (VKPART = :VKPART: OR :VKPART: = 'XXXXX') AND INVDAT >= :VKPNEUDAT:  AND INVDAT <= :VKPAENDDAT: "
                        + " AND (FACTORY = :VKPKOLL: OR :VKPKOLL: = 'XXXXX') AND (CURRENCY = :VKPWAEHRSL: OR :VKPWAEHRSL:='XXXXX') "
                        + " AND (INVOICE = :VKPKDBEZ: OR :VKPKDBEZ:='XXXXX') ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 4:
                    or1.CommandText = "select a.factory,a.currency,a.vkpart,a.vaabez,a.construction,a.color,a.invdat,a.invoice, "
                        + " a.warpden,a.weftden,a.wefttot,a.mdelqty,ROUND(a.yarnvalue/1.17,2) AS yarnvalue,ROUND(a.dyevalue/1.17,2) AS dyevalue, "
                        + " ROUND(a.mcostfin/1.17,2) AS mcostfin,ROUND(a.mcostpakvv/1.17,2) AS mcostpakvv, "
                        + " ROUND((a.mcostfadd+a.mcostagent+a.mcostship+a.mcostflight+a.mcostadd)/1.17,2) as other, "
                        + " ROUND(a.mcostunit/1.17,2) AS mcostunit,ROUND(a.prodvalue*a.weftden) AS prodvalue,a.othersvalue, "
                        + " ROUND(a.mcostunit/1.17+a.prodvalue*a.weftden+a.othersvalue,2) as costunit, "
                        + " ROUND(a.rmbvalue/a.mdelqty,2) AS RMBPRICE,round((a.mcostunit/1.17+a.prodvalue*a.weftden+a.othersvalue)*a.mdelqty,2) as totcost,a.rmbvalue, "
                        + " a.mcostnet,a.costtot,a.costtotw,ROUND(a.prodvalue*a.weftden+a.othersvalue,2) as actcost, "
                        + " round((a.prodvalue*a.weftden+a.othersvalue)*a.mdelqty,2) as acttotcost, "
                        + " round((a.prodvalue*a.weftden+a.othersvalue)/a.weftden*10000) as acttotcostw, "
                        + " a.kdkname,round(a.yarnvalue/1.17*a.mdelqty,2) as totyarn,round(a.dyevalue/1.17*a.mdelqty,2) as totdye, "
                        + " round(a.mcostfin/1.17*a.mdelqty,2) as totfin,round(a.mcostpakvv/1.17*a.mdelqty,2) as totpakvv, "
                        + " round((a.mcostfadd+a.mcostagent+a.mcostship+a.mcostflight+a.mcostadd)/1.17*a.mdelqty,2) as totother,a.commission,a.staff "
                        + " from icc_vkcost a "
                        + " where (VKPART = :VKPART: OR :VKPART: = 'XXXXX') AND INVDAT >= :INVDAT:  AND INVDAT <= :DELDAT: "
                        + " AND (FACTORY = :FACTORY: OR :FACTORY: = 'XXXXX') AND (CURRENCY = :CURRENCY: OR :CURRENCY:='XXXXX') "
                        + " AND (INVOICE = :INVOICE: OR :INVOICE:='XXXXX') AND a.mcostnet>0 ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 5:
                    or1.CommandText = "select substr(invdat,1,6)as mm,a.staff,a.kdkname,sum(a.mdelqty) as mdelqty,sum(a.wefttot) as wefttot, "
                        + " sum(a.rmbvalue) as rmbvalue,sum(a.costtot) as costtot,round(sum(a.costtot)/sum(a.wefttot)*10000,2) as costtotw "
                        + " from icc_vkcost a "
                        + " where INVDAT >= :INVDAT:  AND INVDAT <= :DELDAT: AND a.mcostnet>0 "
                        + " AND (FACTORY = :FACTORY: OR :FACTORY: = 'XXXXX') AND (CURRENCY = :CURRENCY: OR :CURRENCY:='XXXXX') "
                        + " group by a.staff,a.kdkname,substr(invdat,1,6) "
                        ;
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 99:
                    or1.CommandText = "SELECT * FROM ICC_VKCOST  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_VKCOST";
            Return = Result;
            return Return;
        }
        public void DeleteICC_VKCOST(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_VKCOST WHERE ROWNID = :ROWNID: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateICC_VKCOST(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "JLT":
                    #region====Command==== 
                    or1.CommandText = "UPDATE ICC_VKCOST SET "
                    +"INVDAT= :INVDAT:,"
                    +"DELDAT= :DELDAT:,"
                    +"INVOICE= :INVOICE:,"
                    +"RATE= :RATE:,"
                    +"FACTORY= :FACTORY:,"
                    +"WEFTDEN= :WEFTDEN:,"
                    +"CURRENCY= :CURRENCY:,"
                    +"MDELQTY= :MDELQTY:,"
                    +"YDELQTY= :YDELQTY:,"
                    +"WEFTTOT= :WEFTTOT:,"
                    +"WARPUSAGE= :WARPUSAGE:,"
                    +"WARPPRICE= :WARPPRICE:,"
                    +"WEFTUSAGE= :WEFTUSAGE:,"
                    +"WEFTPRICE= :WEFTPRICE:,"
                    +"YARNVALUE= :YARNVALUE:,"
                    +"WARPED= :WARPED:,"
                    +"WEFTED= :WEFTED:,"
                    +"DYEVALUE= :DYEVALUE:,"
                    +"MCOSTFIN= :MCOSTFIN:,"
                    +"MCOSTPAKVV= :MCOSTPAKVV:,"
                    +"MCOSTFADD= :MCOSTFADD:,"
                    +"MPRICEAGENT= :MPRICEAGENT:,"
                    +"MCOSTAGENT= :MCOSTAGENT:,"
                    +"MPRICESHIP= :MPRICESHIP:,"
                    +"MCOSTSHIP= :MCOSTSHIP:,"
                    +"MPRICEFLIGHT= :MPRICEFLIGHT:,"
                    +"MCOSTFLIGHT= :MCOSTFLIGHT:,"
                    +"MCOSTADD= :MCOSTADD:,"
                    +"MCOSTUNIT= :MCOSTUNIT:,"
                    +"RMBPRICE= :RMBPRICE:,"
                    +"MCOSTGROSS= :MCOSTGROSS:,"
                    +"MCOSTNET= :MCOSTNET:,"
                    +"COSTTOT= :COSTTOT:,"
                    +"COSTTOTW= :COSTTOTW:,"
                    +"COMMISSION= :COMMISSION:,"
                    +"NEWART= :NEWART:,"
                    +"PRICE= :PRICE:,"
                    +"INVVALUE= :INVVALUE:,"
                    +"RMBVALUE= :RMBVALUE:,"
                    +"REMARK= :REMARK:,"
                    +"PRODVALUE= :PRODVALUE:,"
                    +"OTHERSVALUE= :OTHERSVALUE:,"
                    +"CHGUSER= :CHGUSER:,"
                    +"CHGTIME= :CHGTIME: "                        
                    + " WHERE  "
                    + "  ROWNID=:ROWNID: ";
                    #endregion
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);                    
                    break;
                case "JLT1":
                    #region====Command====
                    or1.CommandText = "UPDATE ICC_VKCOST SET "
                        + " PRODVALUE= :VKPMENGE1:, "
                        + " OTHERSVALUE= :VKPMENGE2: "
                        + " WHERE (VKPART = :VKPART: OR :VKPART: = 'XXXXX') AND INVDAT >= :VKPNEUDAT:  AND INVDAT <= :VKPAENDDAT: "
                        + " AND (FACTORY = :VKPKOLL: OR :VKPKOLL: = 'XXXXX') AND (CURRENCY = :VKPWAEHRSL: OR :VKPWAEHRSL:='XXXXX') "
                        + " AND (INVOICE = :VKPKDBEZ: OR :VKPKDBEZ:='XXXXX') ";
                    #endregion
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break; 
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
